package com.zbec.mdrs.repository.jpa;


import com.zbec.mdrs.entity.loan.Loan;
import com.zbec.mdrs.vo.LoanVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.query.Param;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

/**
 * Created by fxl on 2015/10/17.
 */
public interface LoanDao extends PagingAndSortingRepository<Loan, Long>, JpaSpecificationExecutor<Loan> {
    @Query("select l from Loan l where l.recordStatus !=3 and l.recordId = :recordId and l.sjsj = :sjsj ")
    List<Loan> getLoanFromDrmap(@Param("recordId")Long recordId,@Param("sjsj")String sjsj);

    @Query( "select max(sjsj) from Loan where sjsj < :sjsj")
    String getMaxLoanFny(@Param("sjsj")String sjsj);

    List<Loan> findByHistoryId(Long historyId);

    List<Loan> findByHistoryIdAndOrgId(Long historyId,Long orgId);


    @Query("select new com.zbec.mdrs.vo.LoanVo(l.id,l.zzjgdm,l.qymc,l.sjsj,l.jrjgbm,l.jrjgwdmc," +
            "l.wdssqu,l.dkhth,l.nbpj,l.wbpj,l.dkjjh,l.bz,l.jbll,l.llfd,l.sjll,l.dklb,l.dkffje," +
            "l.dkye,l.fkrq,l.dqrq,l.zqrq,l.dbfs,l.bzmx,l.bzje,l.dymx,l.dyje,l.zyje,l.qxje,"  +
            "l.zymx,l.wjfl,l.source,l.recordStatus,l.reportStatus,l.existError,l.errorDesc,l.missedField,l.recordId)  from Loan l  " +
            "where (''= :sjsj or l.sjsj = :sjsj) "+
            "and (''= :recordStatus or l.recordStatus = :recordStatus) " +
            "and (''= :existError or l.existError = :existError)  ")
    Page<LoanVo> findAllLoan(@Param("sjsj")String sjsj,@Param("recordStatus")String recordStatus,@Param("existError")String existError,Pageable pageable);


    @Query("select new com.zbec.mdrs.vo.LoanVo(l.id,l.zzjgdm,l.qymc,l.sjsj,l.jrjgbm,l.jrjgwdmc," +
            "l.wdssqu,l.dkhth,l.nbpj,l.wbpj,l.dkjjh,l.bz,l.jbll,l.llfd,l.sjll,l.dklb,l.dkffje," +
            "l.dkye,l.fkrq,l.dqrq,l.zqrq,l.dbfs,l.bzmx,l.bzje,l.dymx,l.dyje,l.zyje,l.qxje,"  +
            "l.zymx,l.wjfl,l.source,l.recordStatus,l.reportStatus,l.existError,l.errorDesc,l.missedField,l.recordId)  from Loan l  " +
            "where (''= :historyId or l.history.id = :historyId) ")
    Page<LoanVo> findLoanByHistoryId(@Param("historyId")String historyId,Pageable pageable);

    @Query("select l from Loan l  " +
            "where (''= :sjsj or l.sjsj = :sjsj) "+
            "and (''= :recordStatus or l.recordStatus = :recordStatus) " +
            "and (''= :existError or l.existError = :existError) order by l.zzjgdm  ")
    List<Loan>  findLoanForExport(@Param("sjsj")String sjsj,@Param("recordStatus")String recordStatus,@Param("existError")String existError);


    @Query("select l from Loan l  " +
            "where l.sjsj = :sjsj  and l.recordStatus <> :recordStatus  and  l.dkjjh = :dkjjh  ")
    List<Loan> findUpdateLoan(@Param("dkjjh")String dkjjh ,@Param("sjsj")String sjsj,@Param("recordStatus")String recordStatus);

    @Query("select l from Loan l  " +
            "where l.sjsj = :sjsj  and l.recordStatus <> :recordStatus  and  l.qymc = :qymc  ")
    List<Loan> findUpdateLoanByName(@Param("qymc")String qymc ,@Param("sjsj")String sjsj,@Param("recordStatus")String recordStatus);

    @Query("select l from Loan l  " +
            "where l.sjsj = :sjsj  and l.recordStatus = :recordStatus  and  l.reportStatus <> :reportStatus " +
            "and (''= :jrjgbm or l.jrjgbm = :jrjgbm) order by l.zzjgdm ")
    List<Loan> getActiveLoanByYwMonth(@Param("sjsj")String sjsj ,@Param("recordStatus")String recordStatus,
                                      @Param("reportStatus")String reportStatus,@Param("jrjgbm")String jrjgbm);

}
